/*
  版权:  @Zach.zhong
  生成日期:2018/2/5   
  说明: T_CustomerReturn_Manage表业务层操作类                      
*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CIT.WMS.houseWork.Model;
using CIT.WMS.houseWork.Helper;

namespace CIT.WMS.houseWork.Bll
{
    class Bll_CustomerReturn_Manage
    {
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="strWhere">where条件</param>
        /// <returns></returns>
        public static DataTable Query(string strWhere)
        {

            string sqlcmd = string.Format(@" select Doc_Number,ReelID,CreateTime,Creator,case Status when '0' then '冻结' when '1' then '解冻' when '2' then '客退' 
end as 'Status',Remark,MaterialNum,Qty from T_CustomerReturn_Manage {0}", strWhere);
            return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="model">T_CustomerReturn_Manage表实体</param>
        /// <returns></returns>
        public static bool Insert(T_CustomerReturn_Manage model)
        {
            string sqlcmd = string.Format(@"insert into T_CustomerReturn_Manage(Doc_Number,ReelID,CreateTime,Creator,Status,Remark,MaterialNum,Qty)
values ('{0}','{1}',getdate(),'{2}','{3}','{4}','{5}','{6}')", model.Doc_Number, model.ReelID, model.Creator, model.Status, model.Remark, model.MaterialNum, model.Qty);
            bool flag;
            bool result;
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return flag;

        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static bool Delete(string strWhere)
        {
            string sqlcmd = string.Format("delete from T_CustomerReturn_Manage '{0}'", strWhere);
            bool flag;
            bool result;
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return flag;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">T_CustomerReturn_Manage表实体</param>
        /// <returns></returns>
        public static bool Update(T_CustomerReturn_Manage model)
        {
            string sqlcmd = string.Format(@"update T_CustomerReturn_Manage set 
				            ReelID='{0}',
				            Status='{1}',
				            Remark='{2}'
				            where Doc_Number='{3}'", model.ReelID, model.Status, model.Remark, model.Doc_Number);
            bool flag;
            bool result;
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return flag;
        }
        #region AnnotateCode
        //        /// <summary>
        //        /// 查询
        //        /// </summary>
        //        /// <param name="varMaterialNum">物料编号</param>
        //        /// <returns></returns>
        //        public static DataTable Query_Use_MaterialNum(string varMaterialNum)
        //        {
        //            string sqlcmd = string.Format("select * from dbo.MdcdatMaterial where MaterialCode='{0}'", varMaterialNum);
        //            return NMS.QueryDataTable(PubUtils.uContext, sqlcmd);
        //        }
        //        /// <summary>
        //        /// 通过物料编号查询物料状态
        //        /// </summary>
        //        /// <param name="varPartNum"></param>
        //        public static DataTable JDAction_Query_PartNumStatus(string varPartNum)
        //        {
        //            string strSql = string.Format(@"
        //--1.校验物料编号是否正确
        //if	not exists (select *  from dbo.MdcdatMaterial where MaterialCode='{0}')
        //	begin
        //		--1.1错误
        //		select '0','物料编号错误' return
        //	end
        //else
        //	begin
        //		--1.2正确（校验库存里是否存在此物料编号）
        //		 if not exists(select *  from dbo.MdcDatVStorage where PartNumber='{0}')
        //			begin
        //				--1.2.1 库存里（不存在）此物料编号
        //				select '0','库存里不存在此物料编号的物料' return
        //			end
        //		 else
        //			begin
        //				--1.2.2 存在
        //				--2.校验库存里的该物料编号的物料是否有被冻结的物料
        //				if not exists(select *  from dbo.T_CustomerReturn_Manage where Status='0' and MaterialNum='{0}')
        //				   begin
        //				     --2.1该物料编号在库存里没有被冻结的
        //					  select '0','该物料编号没有被冻结的物料' return
        //				   end
        //				else	
        //				   begin
        //					 --2.2 该物料编号在库存里存在被冻结的
        //					  select '1','存在被冻结的物料' return
        //				   end
        //			end
        //	end", varPartNum);
        //            return NMS.QueryDataTable(PubUtils.uContext, strSql);
        //        } 
        #endregion
        /// <summary>
        /// 根据料盘编号查询库存状态
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable Query_Storage(string ReelID)
        {
            string sqlcmd = string.Format(@"
declare @partnumber nVarchar(50)
declare @Qty nVarchar(50)
if not exists(select *  from dbo.T_Bllb_StockInfo_tbsi where SerialNumber='{0}')
   begin
		select '0','料盘编号错误' return
   end
else
   begin
	   if exists(select *  from dbo.T_Bllb_StockInfo_tbsi AS a INNER JOIN dbo.T_Bllb_StorageLocation_tbsl AS b ON a.Location_SN=b.Location_SN
	    where a.SerialNumber='{0}' and a.Location_SN<>'' and a.Location_SN<>'NULL'AND b.Location_Type='2')
		  begin
			select '0','料盘上架中无法冻结' return
		  end
      if not  exists(select *  from dbo.T_Bllb_StockInfo_tbsi  WHERE SerialNumber='{0}' and Lock_Flag='0')
		  begin
			select '0','料盘状态无法冻结' return
		  end
       select @partnumber = MaterialCode,@Qty=QTY from dbo.T_Bllb_StockInfo_tbsi where SerialNumber='{0}'
     --校验是否已被冻结
	   if not exists(select *  from dbo.T_CustomerReturn_Manage where ReelID='{0}' and Status='0')
		  begin
			select '1',@partnumber as 'PartNumber',@Qty as 'Qty' return            
		  end
	   else
		  begin
			select '0','料盘已被冻结' return
		  end
    end  ", ReelID);
           return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            
        }

        /// <summary>
        /// 生成单据号
        /// </summary>
        /// <param name="partNum"></param>
        /// <returns></returns>
        public static string CreateDocNum(string partNum)
        {
            string Doc_Num = string.Empty;
            //1.校验该料号 今天是否存在客退单据号
            string sqlcmd = string.Format(@"select * from T_CustomerReturn_Manage where MaterialNum='{0}' AND DATEDIFF(DAY,CreateTime,GETDATE())=0 AND Status='0'", partNum, DateTime.Now.ToString("yyyyMMdd"));
            DataTable dt_MaterialNum = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            if (dt_MaterialNum.Rows.Count > 0)
            {
                //存在，则直接取出单据号
                Doc_Num = SqlInput.ChangeNullToString(dt_MaterialNum.Rows[0]["Doc_Number"]);
            }
            else
            {
                //2.不存在，则查看今天已生成的客退单据号
                string strsql = @"select  distinct Doc_Number from T_CustomerReturn_Manage where DATEDIFF(DAY,CreateTime,GETDATE())=0 order by Doc_Number desc";
                DataTable dt_TodayDocNum = Utils.NMS.QueryDataTable(Utils.uContext, strsql, new CIT.WMS.MESWcf.CmdParameter[0]);
                //2.1 如果今天存在单据号，则要生成的单据号为 已有最大单据号+1
                if (dt_TodayDocNum.Rows.Count > 0)
                {
                    //已有最大单据号+1
                    Doc_Num = (long.Parse(dt_TodayDocNum.Rows[0]["Doc_Number"].ToString()) + 1).ToString();
                }
                else
                {
                    //2.2 如果今天不存在单据号，则要生成的单据号为当天日期+001
                    Doc_Num = DateTime.Now.ToString("yyyyMMdd") + "001";
                }
            }
            return Doc_Num;
        }
        /// <summary>
        /// 通过ReelId查询单据号状态
        /// </summary>
        /// <param name="ReelId"></param>
        /// <returns></returns>
        public static DataTable Query_ReelIdStatus(string ReelId)
        {
            string sqlcmd = string.Format(@"
declare @Doc_Num nVarchar(50)--单据号
--料盘编码是否错误
if not exists(select *  from dbo.MdcDatVStorage where ReelID='{0}')
	begin
		select '0','料盘编码错误' return
	end
else
	begin
		--料盘是否被冻结
		if not exists(select *  from dbo.T_CustomerReturn_Manage where ReelID='{0}' and Status='0')
		    begin
				select '0','料盘未被冻结' return
			end
		else	
			begin
			    select @Doc_Num=Doc_Number  from dbo.T_CustomerReturn_Manage where ReelID='{0}'
				select '1',@Doc_Num return
			end
	end
", ReelId);
            return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
        }

        /// <summary>
        /// 解冻操作
        /// </summary>
        /// <param name="varPartNum"></param>
        /// <returns></returns>
        public static bool JDAction_Update_Status(string strWhere)
        {
            string sqlcmd = string.Format("Update T_CustomerReturn_Manage set Status='1' {0} ", strWhere);
            bool flag;
            bool result;
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return flag;
        }
        /// <summary>
        /// 解冻、客退操作更新备注
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="remarkContent"></param>
        /// <returns></returns>
        public static bool JDKTAction_Update_Remark(string remarkContent, string strWhere)
        {
            string sqlcmd = string.Format("update T_CustomerReturn_Manage set Remark='{0}' {1} ", remarkContent, strWhere);
            bool flag;
            bool result;
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return flag;
        }
        /// <summary>
        /// 客退操作
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static bool KTAction_Update_Status(string strWhere)
        {
            string sqlcmd = string.Format(@"update T_CustomerReturn_Manage set Status='2' {0}", strWhere);
            bool flag;
            bool result;
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return flag;
        }
        /// <summary>
        /// 客退操作删除库存表对应的物料
        /// </summary>
        /// <param name="Doc_num"></param>
        /// <returns></returns>
        public static void KTAction_Delete_VStorage(string Doc_num)
        {
            string sqlcmd = string.Format(@"select ReelID  from T_CustomerReturn_Manage where Doc_Number='{0}'", Doc_num);
            DataTable dt_DNPartNumber = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            foreach (DataRow dr in dt_DNPartNumber.Rows)
            {
                bool flag;
                bool result;
                Utils.NMS.Exectransql(Utils.uContext, string.Format("delete from dbo.MdcDatVStorage where ReelID='{0}'"), new CIT.WMS.MESWcf.CmdParameter[0],out flag,out result);
            }
        }
        /// <summary>
        /// 根据Reelid更新库存表中对应物料的状态标识
        /// </summary>
        /// <param name="varFlag">Flag 0-正常  1-盘点 2-冻结</param>
        /// <param name="lstReelId">reelid集合</param>
        /// <returns></returns>
        public static bool Action_Update_VStorage(string varFlag, List<string> lstReelId)
        {
            if (lstReelId.Count > 0)
            {
                string strWhere = string.Empty;
                foreach (string reelId in lstReelId)
                {
                    if (strWhere == string.Empty)
                    {
                        strWhere += string.Format("and SerialNumber in ('{0}'", reelId);
                    }
                    else
                    {
                        strWhere += string.Format(",'{0}'", reelId);
                    }
                }
                if (strWhere != string.Empty)
                {
                    strWhere += " )";
                }
                string sqlcmd = string.Format(" update dbo.T_Bllb_StockInfo_tbsi set Lock_Flag='{0}' where 1=1 {1}", varFlag, strWhere);
                bool flag;
                bool result;
                Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0],out flag,out result);
                return flag;
            }
            return true;
        }
    }
}
